# Autosub Db.py - http://code.google.com/p/auto-sub/
#
# The Autosub DB module
# 

import os
import sqlite3
import logging

# debug purposes, remove when fully integrated
import autosub
autosub.DATABASEFILE = 'database.db'

class dbCon:
    def __init__(self):
        self.databasecon = sqlite3.connect(autosub.DATABASEFILE)
        self.databasecursor = self.databasecon.cursor()
    def runQuery(self, query, arg):
        self.databasecursor.execute(query, arg)
        try:
            self.databasecon.commit()
        except:
            pass
        return self.databasecursor

# Settings
log = logging.getLogger('thelogger')

def initDatabase():
    databasecon = sqlite3.connect(autosub.DATABASEFILE)
    databasecursor = databasecon.cursor()
    #check if file is already there, if not raise error

    #if os.path.exists(autosub.DATABASEFILE):
        #return False
    
    #create the database
    try:    
        #create the tables 
        databasecursor.execute("CREATE TABLE tv_shows (show_id INTEGER PRIMARY KEY, bierdopje_id INTEGER, thetvdb_id INTEGER, show_name TEXT, show_url TEXT, LocationOnDisk TEXT, status TEXT, nlwanted INTEGER, enwanted INTEGER);")
        #cursor.execute("CREATE TABLE tv_episodes (episode_id INTEGER PRIMARY KEY, show_bierdopje_id NUMERIC, title TEXT, season NUMERIC, episode NUMERIC, status NUMERIC, originalFileLocationOnDisk TEXT, quality TEXT, releasegrp TEXT, source TEXT);")
        #cursor.execute("CREATE TABLE status (lastRSScheck NUMERIC, lastScanDir NUMERIC, lastFullBierdopjeScan NUMERIC, lastDownload NUMERIC);")
        #cursor.execute("CREATE TABLE history (action NUMERIC, date NUMERIC, show_bierdopje_id NUMERIC, season NUMERIC, episode NUMERIC, downloadedFilename TEXT);")
        databasecursor.execute("CREATE TABLE id_cache (bierdopje_id INTEGER, ShowName TEXT);")
        databasecursor.execute("CREATE TABLE localname (bierdopje_id INTEGER, LocalName TEXT);")
        databasecursor.execute("CREATE TABLE info (autoSubVersion NUMERIC, databaseVersion NUMERIC);")
        databasecursor.execute("CREATE TABLE episodes (episode_id INTEGER PRIMARY KEY, show_id INTEGER, episode INTEGER, season INTEGER, source TEXT, quality TEXT, releasegrp TEXT, nlwanted INTEGER, enwanted INTEGER, downloadedsubnl TEXT, downloadedsuben TEXT, localvideofile TEXT, nlsrtfile TEXT, ensrtfile TEXT, airdate TEXT);")
        databasecon.commit()
        
        log.debug("initDatabase: Succesfully created the sqlite database")
    except:
        log.error("initDatabase: Could not create database, please check if AutoSub has write access to write the following file %s" %autosub.DATABASEFILE)
    
    return True

def wipeDatabase():
    
    if not os.path.exists(autosub.DATABASEFILE):
        return False
    
    try:   
        databasecursor.execute("DELETE FROM tv_shows")
        #cursor.execute("DELETE FROM tv_episodes")
        #cursor.execute("DELETE FROM history")
        #cursor.execute("DELETE FROM status")
        
        databasecon.commit()
        
        log.debug("wipeDatabase: Succesfully wiped database")
    except:
        log.error("wipeDatabase: Could not wipe database, please delete the file manually at %s" %autosub.DATABASEFILE)
        
    return True

def testDb():
    
    if not os.path.exists(autosub.DATABASEFILE):
        log.error("testDb: No database found, could not preform tests")
        return False
    noError = True
    
    connection=sqlite3.connect(autosub.DATABASEFILE) 
    cursor=connection.cursor()
    
    testShows = "SELECT * FROM tv_shows;"
    testEpisodes = "SELECT * FROM tv_episodes;"
    testStatus ="SELECT * FROM status;"
    testHistory = "SELECT * FROM history;"
    testInfo = "SELECT * FROM info;"

    response = None
    
    try:
        response = cursor.execute(testShows)
        log.debug("testDb: Database test of shows table succeeded, amount of rows: %s"  %len(response.fetchall()))
    except:
        noError = False
        log.error("testDb: Database test of shows table failed!!")
    
    try:
        response = cursor.execute(testEpisodes)
        log.debug("testDb: Database test of episodes table succeeded, amount of rows: %s"  %len(response.fetchall()))
    except:
        noError = False
        log.error("testDb: Database test of episodes table failed!!")    

    try:
        response = cursor.execute(testStatus)
        log.debug("testDb: Database test of status table succeeded, amount of rows: %s"  %len(response.fetchall()))
    except:
        noError = False
        log.error("Database test of status table failed!!")

    try:
        response = cursor.execute(testHistory)
        log.debug("testDb: Database test of history table succeeded, amount of rows: %s"  %len(response.fetchall()))
    except:
        noError = False
        log.error("testDb: Database test of history table failed!!")
        
    try:
        response = cursor.execute(testInfo)
        log.debug("testDb: Database test of info table succeeded, amount of rows: %s"  %len(response.fetchall()))
    except:
        noError = False
        log.error("testDb: Database test of info table failed!!")

    cursor.close()
    connection.close()

    if noError == True:
        log.debug("All database tests passed")
        return True
    else:
        return False
    